package com.gyul.findmystock.db;

import java.util.ArrayList;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.gyul.findmystock.entity.QuoteEntity;
import com.gyul.findmystock.util.Constants;


public class DaoQuote extends DataAccessObject<QuoteEntity> {		
	public String QUOTE_TABLE = "QuoteTable_";
	public static final String QUOTE_DATE = "QuoteDate";
	public static final String QUOTE_LOW = "QuoteLow";
	public static final String QUOTE_HIGH = "QuoteHigh";
	public static final String QUOTE_OPEN = "QuoteOpen";
	public static final String QUOTE_CLOSE = "QuoteClose";
	public static final String QUOTE_VOLUME = "QuoteVolume";
		
	public DaoQuote(String TAG, String symbol) {		
		super(TAG + " - DaoQuote");
		QUOTE_TABLE = QUOTE_TABLE + symbol;
	}
	
	public void createTable() {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("CREATE TABLE IF NOT EXISTS " + QUOTE_TABLE + " ( " +
				QUOTE_DATE + " INTEGER PRIMARY KEY, " +
				QUOTE_LOW + " INTEGER NOT NULL, " +
				QUOTE_HIGH + " INTEGER NOT NULL, " +
				QUOTE_OPEN + " INTEGER NOT NULL, " +
				QUOTE_CLOSE + " INTEGER NOT NULL, " +				
				QUOTE_VOLUME + " INTEGER NOT NULL );");
		//db.close();
	}
	
	public void dropTable() {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + QUOTE_TABLE);
		//db.close();
	}
	
	public void initInsert(ArrayList<QuoteEntity> dataList) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.beginTransaction();
		
		try {
			for ( QuoteEntity data : dataList ) {
				try {				
					db.execSQL("INSERT INTO " + QUOTE_TABLE + 
							" (" + QUOTE_DATE + ", " + QUOTE_LOW + ", " + 
							QUOTE_HIGH + ", " + QUOTE_OPEN + ", " + QUOTE_CLOSE + ", " + QUOTE_VOLUME + ") " +
							"VALUES (" + data.date + ", " + data.low + "," +
										data.high + ", " + data.open + ", " + 
										data.close + ", " + data.volume + ");");
				} catch ( Exception e ) {
					Log.e(TAG, e.getMessage());
					Log.e(TAG, "Insert : " + data.toString());
				}
			}		
			db.setTransactionSuccessful();
		} catch ( Exception e ) {
			
		} finally {
			db.endTransaction();
			//db.close();
		}
	}
	
	public void insert(ArrayList<QuoteEntity> dataList) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.beginTransaction();
		
		try {		
			for ( QuoteEntity data : dataList ) {				
				try {				
					db.execSQL("INSERT OR REPLACE INTO " + QUOTE_TABLE + 
							" (" + QUOTE_DATE + ", " + QUOTE_LOW + ", " + 
							QUOTE_HIGH + ", " + QUOTE_OPEN + ", " + QUOTE_CLOSE + ", " + QUOTE_VOLUME + ") " +
							"VALUES (" + data.date + ", " + data.low + "," +
										data.high + ", " + data.open + ", " + 
										data.close + ", " + data.volume + ");");
				} catch ( Exception e ) {
					Log.e(TAG, e.getMessage());
					Log.e(TAG, "Insert : " + data.toString());
				}
			}
			db.setTransactionSuccessful();
		} catch ( Exception e ) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			//db.close();
		}
	}
	
	public void insert(QuoteEntity data) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("INSERT OR REPLACE INTO " + QUOTE_TABLE + 
				" (" + QUOTE_DATE + ", " + QUOTE_LOW + ", " + 
				QUOTE_HIGH + ", " + QUOTE_OPEN + ", " + QUOTE_CLOSE + ", " + QUOTE_VOLUME + ") " +
				"VALUES (" + data.date + ", " + data.low + "," +
							data.high + ", " + data.open + ", " + 
							data.close + ", " + data.volume + ");");
		
		//db.close();
	}
	
	public void update(QuoteEntity data) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("INSERT OR REPLACE INTO " + QUOTE_TABLE + 
				" (" + QUOTE_DATE + ", " + QUOTE_LOW + ", " + 
				QUOTE_HIGH + ", " + QUOTE_OPEN + ", " + QUOTE_CLOSE + ", " + QUOTE_VOLUME + ") " +
				"VALUES (" + data.date + ", " + data.low + "," +
							data.high + ", " + data.open + ", " + 
							data.close + ", " + data.volume + ");");
		//db.close();
	}
	
	public ArrayList<QuoteEntity> select(boolean asc) {
		SQLiteDatabase db = helper.getReadableDatabase();
		ArrayList<QuoteEntity> list = new ArrayList<QuoteEntity> ();
		Cursor c; 
		if ( asc ) {
			c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE + " ASC;", null);
		} else {
			c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE + " DESC;", null);
		}
		while ( c.moveToNext() ) {
			list.add(new QuoteEntity(c.getInt(0), 
								c.getInt(1),
								c.getInt(2),
								c.getInt(3),
								c.getInt(4),								
								c.getInt(5)));
		}
		c.close();
		//db.close();
		return list; 
	}
	
	public int [] selectClose() {
		int index = 0;
		int [] close = new int[getSize()];
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT " + QUOTE_CLOSE + " FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE + " ASC;", null);
		
		while ( c.moveToNext() ) {
			close[index++] = c.getInt(0);
		}	
		
		c.close();
		//db.close();
		
		return close;
	}
	
	public ArrayList<QuoteEntity> select(int tableIndex) {		
		SQLiteDatabase db = helper.getReadableDatabase();
		ArrayList<QuoteEntity> list = new ArrayList<QuoteEntity> ();
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE +
				" DESC LIMIT " + Constants.PRICE_TABLE_SIZE + 
				" OFFSET " + (tableIndex*Constants.PRICE_TABLE_SIZE) + ";", null);
		
		while ( c.moveToNext() ) {
			list.add(new QuoteEntity(c.getInt(0), 
								c.getInt(1),
								c.getInt(2),
								c.getInt(3),
								c.getInt(4),								
								c.getInt(5)));
		}
		
		c.close();
		//db.close();
		return list;
	}
	
	public QuoteEntity getLastQuote() {
		QuoteEntity quote = null;
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE +
				" DESC LIMIT 1;", null);
		
		if ( c.moveToNext() ) {
			quote = new QuoteEntity(c.getInt(0), 
								c.getInt(1),
								c.getInt(2),
								c.getInt(3),
								c.getInt(4),								
								c.getInt(5));
		}
		c.close();
		//db.close();
		return quote; 
	}
	
	
	public QuoteEntity get( QuoteEntity key) {
		SQLiteDatabase db = helper.getReadableDatabase();		
		QuoteEntity quote = null;
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " WHERE " + QUOTE_DATE + " = " + key.date + ";", null);
		while ( c.moveToNext() ) {
			quote = new QuoteEntity(c.getInt(0), 
					c.getInt(1),
					c.getInt(2),
					c.getInt(3),
					c.getInt(4),								
					c.getInt(5));
		}
		
		c.close();
		//db.close();
		
		return quote; 
	}
	
	public int getSize() {
		int size = 0;
		SQLiteDatabase db = helper.getReadableDatabase();
		
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + ";", null);
		
		size = c.getCount();
		c.close();
		//db.close();
		return size;
	}
	
	public int getDate(int index) {
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE +
				" ASC LIMIT " + (index+1) + ";", null);
		
		if ( c.moveToLast() ) {
			return c.getInt(c.getColumnIndex(QUOTE_DATE));
		}
		
		c.close();
		//db.close();
		
		return 0;
	}
	
	public int getLastDate() {
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + QUOTE_TABLE + " ORDER BY " +  QUOTE_DATE +
				" DESC LIMIT 1;", null);
		
		if ( c.moveToNext() ) {
			return c.getInt(c.getColumnIndex(QUOTE_DATE));
		}
		c.close();
		//db.close();
		
		return 0; 
	}
}
